﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Clinica_Frba.AbmdeProfesional
{
    public partial class Alta : Form
    {
        public Alta()
        {
            InitializeComponent();
        }

        private void Alta_Load(object sender, EventArgs e)
        {
            radioButton1.Checked = true;
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            string comando;
            comando = "SELECT * FROM DUNDER_MIFFLIN.ESPECIALIDAD";
            SqlCommand consulta = new SqlCommand(comando, myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("id_esp");
            dt.Columns.Add("desc_esp");
            dt.Load(da);
            listBox1.DisplayMember = "desc_esp";
            listBox1.ValueMember = "id_esp";
            listBox1.DataSource = dt;
            listBox1.SelectedIndex = 0;
            myConnection.Close();
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            if (radioButton1.Checked) { radioButton2.Checked = false; }
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            if (radioButton2.Checked) { radioButton1.Checked = false; }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listBox1.Items.Count != 0)
            {
                bool ver = true;
                int i;

                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    if (listBox2.GetItemText(listBox2.Items[i]) == listBox1.GetItemText(listBox1.SelectedItem)) { ver = false; }
                }
                if (ver)
                {

                    listBox2.Items.Add(listBox1.GetItemText(listBox1.SelectedItem));

                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (listBox2.Items.Count != 0)
            {
                listBox2.Items.Remove(listBox2.SelectedItem);
                if (listBox2.Items.Count > 0) { listBox2.SelectedIndex = 0; }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
            listBox2.Items.Clear();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "") { return; }
            if (textBox2.Text == "") { return; }
            if (textBox3.Text == "") { return; }
            if (textBox6.Text == "") { return; }
            if (listBox2.Items.Count == 0) { return; }
            string fecha;
            DateTime date;
            fecha = textBox7.Text + "/" + textBox8.Text + "/" + textBox9.Text;
            if (DateTime.TryParse(fecha, out date)) { } else { return; }
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand matric = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.PROFESIONAL WHERE MATRICULA='" + textBox6.Text + "'", myConnection);
            SqlDataReader ma = matric.ExecuteReader();
            if (ma.HasRows) { MessageBox.Show("Numero de matricula repetido"); return; }
            ma.Close();
            SqlCommand beg = new SqlCommand("BEGIN TRAN P", myConnection);
            beg.ExecuteNonQuery();
            int id,i;
            try
            {
                SqlCommand ii = new SqlCommand("SET IDENTITY_INSERT DUNDER_MIFFLIN.PROFESIONAL ON", myConnection);
                ii.ExecuteNonQuery();
                SqlCommand prueba = new SqlCommand("SELECT NRO_DOC FROM DUNDER_MIFFLIN.PROFESIONAL WHERE NRO_DOC='" + textBox3.Text + "'", myConnection);
                SqlDataReader s = prueba.ExecuteReader();

                if (s.Read()) { MessageBox.Show("DNI repetido", "Alta de profesional"); return; }
                s.Close();
                SqlCommand com = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='PROFESIONAL'", myConnection);
                SqlDataReader SQLR = com.ExecuteReader();
                SQLR.Read();
         
                id = Convert.ToInt32(SQLR.GetValue(0));
                SQLR.Close();
                id++;
                int idfu;
                string sx;
                sx = "N";
                if (radioButton1.Checked) { sx = "M"; }
                if (radioButton2.Checked) { sx = "F"; }
                SqlCommand insert = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.PROFESIONAL(ID_PROF,NRO_PROF,Nombre,Apellido,TIPO_DOC,NRO_DOC,Direccion,Telefono,Mail,Matricula,FECHA_NACIMIENTO,Sexo) VALUES(" + id + "," + id + ",'" + textBox1.Text + "','" + textBox2.Text + "','DNI','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox10.Text + "','" + textBox6.Text + "',CONVERT(datetime,'" + fecha + "',103),'" + sx + "')", myConnection);
                insert.ExecuteNonQuery();
                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    SqlCommand idf = new SqlCommand("SELECT ID_ESP FROM DUNDER_MIFFLIN.ESPECIALIDAD WHERE DESC_ESP='" + listBox2.GetItemText(listBox2.Items[i]) + "'", myConnection);

                    SqlDataReader func = idf.ExecuteReader();
                    func.Read();
                    idfu = Convert.ToInt32(func.GetValue(0));
                    func.Close();
                    SqlCommand insert2 = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD(ID_PROF,ID_ESP) VALUES(" + id + "," + idfu + ")", myConnection);
                    insert2.ExecuteNonQuery();
                }
                SqlCommand provider = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + id + " WHERE TABLA='PROFESIONAL'", myConnection);

                provider.ExecuteNonQuery();
                SqlCommand c = new SqlCommand("COMMIT TRAN R", myConnection);
                c.ExecuteNonQuery();
                MessageBox.Show("Insercion ok", "Alta de profesional");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Alta de profesional");
                SqlCommand rb = new SqlCommand("ROLLBACK TRAN P", myConnection);
                rb.ExecuteNonQuery();
            }
            
        }
    }
}
